/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.database;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleBlobInputStream;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import org.property.Estate;
import org.property.House;
import org.users.Agent;
import org.users.Customer;
import org.users.Supervisor;

/**
 *
 * @author Emre
 */
public class DatabaseEmre {

    private Connection connection = null;
    private String userName;
    private String password;
    private String portNumber;

    public DatabaseEmre() {
        userName = "user";
        password = "1234";
        portNumber = "1521";


    }

    public void connect() throws ClassNotFoundException, SQLException {

        Class.forName("oracle.jdbc.OracleDriver");
        System.out.println("Class Found");
        connection = DriverManager.getConnection("jdbc:oracle:thin:" + userName + "/" + password + "@localhost:" + portNumber + ":XE");
        System.out.println("connected !");

    }

    //ADD_CUSTOMER('12345678902','Emre','GÖZEN','06088972312','emregozen@gmail.com',sysdate-15,1,1,'Alaattin Tepesi');
    public void addCustomer(Customer customer) throws SQLException {
        String addCustomerStoredProcedure = "{ call ADD_CUSTOMER(?, ?, ?,?, ?, ? ,?, ?, ?) }";
        CallableStatement cs = connection.prepareCall(addCustomerStoredProcedure);
        java.sql.Date sqlDate = new java.sql.Date(customer.getBirthDate().getYear(), customer.getBirthDate().getMonth(), customer.getBirthDate().getDate());
        // sqlDate = (java.sql.Date)date;
        cs.setString(1, customer.getTC_ID());
        cs.setString(2, customer.getName());
        cs.setString(3, customer.getSurname());
        cs.setString(4, customer.getGsmNumber());
        cs.setString(5, customer.geteMail());
        cs.setDate(6, sqlDate);
        cs.setInt(7, Integer.parseInt(customer.getAddressProvince()));
        cs.setInt(8, Integer.parseInt(customer.getAddressDistrict()));
        cs.setString(9, customer.getAddressResume());

        cs.execute();
    }

    public void updateCustomer(Customer customer) throws SQLException {
        String addCustomerStoredProcedure = "{ call UPDATE_CUSTOMER(?, ?, ?,?, ?, ? ,?, ?, ?) }";
        CallableStatement cs = connection.prepareCall(addCustomerStoredProcedure);
        java.sql.Date sqlDate = new java.sql.Date(customer.getBirthDate().getYear(), customer.getBirthDate().getMonth(), customer.getBirthDate().getDate());
        // sqlDate = (java.sql.Date)date;
        cs.setString(1, customer.getTC_ID());
        cs.setString(2, customer.getName());
        cs.setString(3, customer.getSurname());
        cs.setString(4, customer.getGsmNumber());
        cs.setString(5, customer.geteMail());
        cs.setDate(6, sqlDate);
        cs.setInt(7, Integer.parseInt(customer.getAddressProvince()));
        cs.setInt(8, Integer.parseInt(customer.getAddressDistrict()));
        cs.setString(9, customer.getAddressResume());
        cs.execute();
    }

    public void deleteCustomer(Customer customer) throws SQLException {
        String deleteCustomerStoredProcedure = "{ call DELETE_CUSTOMER(?) }";
        CallableStatement cs = connection.prepareCall(deleteCustomerStoredProcedure);
        cs.setString(1, customer.getTC_ID());
        cs.execute();
    }

    public List<Customer> getCustomerList() {
        List<Customer> customerList = new LinkedList<Customer>();
        try {

            String query = "select person.*,customer.*, district.name as d_name, province.name as p_name  from customer,person,district  ,province  ";
            query += "where (customer.isdeleted ='N' or customer.isdeleted ='n') and person.tc_id = customer.customer_tc_id and district.d_id = person.d_id and province.p_id = person.p_id ";
            Statement statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            java.util.Date birthDate;
            java.util.Date registerDate;
            while (rs.next()) {
                birthDate = new Date(rs.getDate("BIRTH_DATE").getTime());
                registerDate = new Date(rs.getDate("ADDINGTIME").getTime());
                customerList.add(new Customer(rs.getString("TC_ID"), rs.getString("name"), rs.getString("surname"), rs.getString("GSM_NUMBER"), rs.getString("E_MAIL"), birthDate, rs.getString("D_NAME"), rs.getString("ADDRESS_RESUME"), rs.getString("P_NAME"), registerDate));

            }

        } catch (SQLException ex) {
            Logger.getLogger(DatabaseYakup.class.getName()).log(Level.SEVERE, null, ex);
        }

        return customerList;



    }

    public Map<String, String> getCitiesMap() throws SQLException {
        Map<String, String> cities = new HashMap<String, String>();
        String query = "Select * FROM PROVINCE";
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            cities.put(rs.getString("NAME"), Integer.toString(rs.getInt("P_ID")));

        }
        return cities;

    }

    public Map<String, Map<String, String>> getSuburbsMap() throws SQLException {
        Map<String, String> suburbs = null;
        Map<String, Map<String, String>> suburbsData = new HashMap<String, Map<String, String>>();
        String query = "select province.*,district.name as d_name,district.d_id from province,district where province.p_id = district.p_id order by province.p_id";
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        int oldId = 0;
        while (rs.next()) {
            if (oldId != rs.getInt("P_ID")) {
                if (suburbs != null) {
                    suburbsData.put(Integer.toString(oldId), suburbs);
                }
                suburbs = new HashMap<String, String>();


                suburbs.put(rs.getString("D_NAME"), Integer.toString(rs.getInt("D_ID")));
            } else {
                suburbs.put(rs.getString("D_NAME"), Integer.toString(rs.getInt("D_ID")));
            }
            oldId = rs.getInt("P_ID");
        }
        if (suburbs != null) {
            suburbsData.put(Integer.toString(oldId), suburbs);
        }


        return suburbsData;

    }

//     private Map<String,Map<String,String>> suburbsData = new HashMap<String, Map<String,String>>();
//	
//	private Map<String,String> suburbs = new HashMap<String, String>();
//
//	public PprBean() {
//		cities.put("Istanbul", "1");
//		cities.put("Ankara", "2");
//		cities.put("Izmir", "3");
//		
//		Map<String,String> suburbsIstanbul = new HashMap<String, String>();
//		suburbsIstanbul.put("Kadikoy", "1");
//		suburbsIstanbul.put("Levent", "2");
//		suburbsIstanbul.put("Cengelkoy", "3");
//		
//		Map<String,String> suburbsAnkara = new HashMap<String, String>();
//		suburbsAnkara.put("Kecioren", "1");
//		suburbsAnkara.put("Cankaya", "2");
//		suburbsAnkara.put("Yenimahalle", "3");
//		
//		Map<String,String> suburbsIzmir = new HashMap<String, String>();
//		suburbsIzmir.put("Cesme", "1");
//		suburbsIzmir.put("Gumuldur", "2");
//		suburbsIzmir.put("Foca", "3");
//		
//		suburbsData.put("1", suburbsIstanbul);
//		suburbsData.put("2", suburbsAnkara);
//		suburbsData.put("3", suburbsIzmir);
//	}
//    
//  
    public String loginSupervisor(Supervisor supervisor) throws SQLException {

//        CallableStatement callableStatement = connection.prepareCall("{? = LOGIN_SUPERVISOR (v_sup_tc_id => ? ,v_user_name => ? , v_password => ? )}");
//        callableStatement.registerOutParameter(1, OracleTypes.BOOLEAN);
//        callableStatement.registerOutParameter(2, OracleTypes.CHAR);
//        callableStatement.setString(2, supervisor.getTC_ID());
//        callableStatement.setString(3, supervisor.getUserName());
//        callableStatement.setString(4, supervisor.getPassword());
//        System.out.println("buraya kadar");
        String query = "SELECT SUP_TC_ID FROM SUPERVISOR WHERE USER_NAME = ? AND PASSWORD = ?";
        PreparedStatement stmt = connection.prepareStatement(query);
        stmt.setString(1, supervisor.getUserName());
        stmt.setString(2, supervisor.getPassword());
        ResultSet rs = stmt.executeQuery();
        String tcId;
        if (rs.next()) {
            tcId = rs.getString(1);
            return tcId;
        } else {
            return "none";
        }




    }

    public Supervisor getSupervisor(String supervisorId) throws SQLException {
        String query = "select p.*,s.*, d.name as district_name,pro.name as province_name from person p ,  SUPERVISOR s,district d,province pro   where s.sup_tc_id = ? and p.tc_id = s.sup_tc_id and p.d_id = d.d_id and p.p_id = pro.p_id ";
        PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, supervisorId);
        ResultSet rs = statement.executeQuery();
        Supervisor supervisor = null;
        while (rs.next()) {
            // String userName, String password, String TC_ID, String name, String surname, String gsmNumber, String eMail, Date birthDate, String addressDistrict, String addressResume)
            java.util.Date birthDate = rs.getDate("birth_date");

            supervisor = new Supervisor(rs.getString("user_name"), rs.getString("password"), rs.getString("tc_id"), rs.getString("name"), rs.getString("surname"), rs.getString("gsm_number"), rs.getString("e_mail"), birthDate, rs.getString("district_name"), rs.getString("address_resume"), rs.getString("province_name"));
        }

        return supervisor;
    }

    public LinkedList<Estate> getEstateList() throws SQLException {
        String query = "select estate_id,image_path,price,decode(type,1,'Sale',2,'Rent') as type ,isConstruction,countryside,perceptofconstruction,district.name as d_name,province.name as p_name,address_resume,description from estate,district,province where sold <> 'Y' and district.d_id = estate.d_id and province.p_id = district.p_id";
        Statement statement = connection.createStatement();
        LinkedList<Estate> estates = new LinkedList<Estate>();
        LinkedList<String> images = new LinkedList<String>();
        ResultSet rs = statement.executeQuery(query);
        //ESTATE_ID IMAGE_PATH PRICE TYPE ISCONSTRUCTION COUNTRYSIDE PERCEPTOFCONSTRUCTION D_NAME P_NAME ADDRESS_RESUME DESCRIPTION 
        while (rs.next()) {

            rs.getString("IMAGE_PATH");
            rs.getInt("PRICE");
            rs.getString("TYPE");
            rs.getString("ISCONSTRUCTION");
            rs.getString("COUNTRYSIDE");
            rs.getInt("PERCEPTOFCONSTRUCTION");
            rs.getString("D_NAME");
            rs.getString("P_NAME");
            rs.getString("ADDRESS_RESUME");
            rs.getString("DESCRIPTION");
            boolean tmpcs, tmpcnt;
            if (rs.getString("ISCONSTRUCTION").equals("Y")) {
                tmpcs = true;
            } else {
                tmpcs = false;
            }
            if (rs.getString("COUNTRYSIDE").equals("Y")) {
                tmpcnt = true;
            } else {
                tmpcnt = false;
            }



            images = getEstateImagePathList(rs.getInt("ESTATE_ID"));
            //int id, String type, int price, boolean isConstruction, boolean isCountryside, int perceptOfConstruction,List<String> imageArray, String district, String province,  String address, String agentId, String customerId,String description

            estates.add(new Estate(rs.getInt("ESTATE_ID"), rs.getString("TYPE"), rs.getInt("PRICE"), tmpcnt, tmpcs, rs.getInt("PERCEPTOFCONSTRUCTION"), images, rs.getString("D_NAME"), rs.getString("P_NAME"), rs.getString("ADDRESS_RESUME"), "null", "null", rs.getString("DESCRIPTION")));

        }
        return estates;
    }

    private LinkedList<String> getEstateImagePathList(int id) throws SQLException {
        LinkedList<String> imageList = new LinkedList<String>();
        String query = "select image_paths.image_path from estate,image_paths,estate_images where estate.estate_id =? and estate.estate_id = estate_images.estate_id and image_paths.image_id = estate_images.image_id";
        PreparedStatement ps = connection.prepareStatement(query);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            imageList.add(rs.getString("IMAGE_PATH"));

        }



        return imageList;

    }

    public void deleteEstate(Estate selectedEstate) {
        try {
            String deleteEstate = "call DELETE_ESTATE(?)";
            CallableStatement cs = connection.prepareCall(deleteEstate);
            cs.setInt(1, selectedEstate.getId());
            cs.execute();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseYakup.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public String addAgent(Agent agent) throws SQLException {
        //cust_id CHAR,name varchar2,surname varchar2,gsm_number char,e_mail varchar2, birth_date date,dist_id number,pro_id number,address_resume varchar2,v_user_name VARCHAR2,pass out varchar2 
        String addAgentStoredProcedure = "{  call ADD_AGENT(?, ?, ?,?, ?, ? ,?, ?, ?,?,?) }";
        CallableStatement cs = connection.prepareCall(addAgentStoredProcedure);
        java.sql.Date sqlBirthDate = new java.sql.Date(agent.getBirthDate().getYear(), agent.getBirthDate().getMonth(), agent.getBirthDate().getDay());
        cs.registerOutParameter(11, OracleTypes.VARCHAR);
        cs.setString(1, agent.getTC_ID());
        cs.setString(2, agent.getName());
        cs.setString(3, agent.getSurname());
        cs.setString(4, agent.getGsmNumber());
        cs.setString(5, agent.geteMail());
        cs.setDate(6, sqlBirthDate);
        cs.setInt(7, Integer.parseInt(agent.getAddressDistrict()));
        cs.setInt(8, Integer.parseInt(agent.getAddressProvince()));
        cs.setString(9, agent.getAddressResume());
        cs.setString(10, agent.getSurname() + agent.getSurname());
        cs.execute();
        String rs = (String) cs.getObject(11);

        return rs;
    }

    public void deleteHouse(House house) throws SQLException {
        String deleteHouseStoredProcedure = "{ call DELETE_HOUSE(?) }";
        CallableStatement cs = connection.prepareCall(deleteHouseStoredProcedure);
        cs.setInt(1, house.getId());
        cs.execute();
    }

    public void addHouse(House house) throws SQLException {
//v_room_id number,v_price NUMBER,v_type NUMBER,v_description char,v_hascombi char,
//v_hasdooubleglazing char,v_hasbalcony char,v_hascabletv char,v_haselevator char,v_hasfirealarm char,
//v_haspark char,v_img_array IMAGE_ARRAY,v_d_id number, v_p_id number            
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("IMAGE_ARRAY", connection);
        String[] images = new String[house.getImageList().size()];
        house.getImageList().toArray(images);
        ARRAY imageArrayToPass = new ARRAY(descriptor, connection, images);
        String combi = house.isIsCombi() ? "y" : "n";
        String glazing = house.isIsDoubleGlazing() ? "y" : "n";
        String balcony = house.isIsBalcony() ? "y" : "n";
        String cabletv = house.isIsCableTV() ? "y" : "n";
        String elevator = house.isIsElevator() ? "y" : "n";
        String firealarm = house.isIsFireAlarm() ? "y" : "n";
        String park = house.isIsPark() ? "y" : "n";

        String addHouseStoredProcedure = "{ call ADD_HOUSE(?, ?, ?,?, ?, ? ,?, ?, ?,?,?,?,?,?) }";
        CallableStatement cs = connection.prepareCall(addHouseStoredProcedure);
        //java.sql.Date sqlDate = new java.sql.Date(date.getYear(), date.getMonth(), date.getDate());
        // sqlDate = (java.sql.Date)date;
        cs.setInt(1, house.getId());
        cs.setInt(2, house.getPrice());
        cs.setString(3, house.getType());
        cs.setString(4, house.getDescription());
        cs.setString(5, combi);
        cs.setString(6, glazing);
        cs.setString(7, balcony);
        cs.setString(8, cabletv);
        cs.setString(9, elevator);
        cs.setString(10, firealarm);
        cs.setString(11, park);
        cs.setArray(12, imageArrayToPass);
        cs.setString(13, house.getDistrict());
        cs.setString(14, house.getProvince());

        cs.execute();
    }

    public void updateHouse(House house) throws SQLException {
        ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("IMAGE_ARRAY", connection);
        //String[] images = new String[house.getImageList().size()];
        //house.getImageList().toArray(images);
        //ARRAY imageArrayToPass = new ARRAY(descriptor, connection, images);

        String addHouseStoredProcedure = "{ call UPDATE_HOUSE(?, ?, ?,?, ?, ? ,?, ?, ?,?,?,?,?,?) }";
        CallableStatement cs = connection.prepareCall(addHouseStoredProcedure);
        //java.sql.Date sqlDate = new java.sql.Date(date.getYear(), date.getMonth(), date.getDate());
        //sqlDate = (java.sql.Date)date;
        String combi = house.isIsCombi() ? "y" : "n";
        String glazing = house.isIsDoubleGlazing() ? "y" : "n";
        String balcony = house.isIsBalcony() ? "y" : "n";
        String cabletv = house.isIsCableTV() ? "y" : "n";
        String elevator = house.isIsElevator() ? "y" : "n";
        String firealarm = house.isIsFireAlarm() ? "y" : "n";
        String park = house.isIsPark() ? "y" : "n";

        cs.setInt(1, Integer.parseInt(house.getRoom_type()));
        cs.setInt(2, house.getPrice());
        cs.setInt(3, Integer.parseInt(house.getType()));
        cs.setString(4, house.getDescription());
        cs.setString(5, combi);
        cs.setString(6, glazing);
        cs.setString(7, balcony);
        cs.setString(8, cabletv);
        cs.setString(9, elevator);
        cs.setString(10, firealarm);
        cs.setString(11, park);
        cs.setInt(12, Integer.parseInt(house.getDistrict()));
        cs.setInt(13, Integer.parseInt(house.getProvince()));
        cs.setInt(14, house.getId());

        cs.execute();
    }

    private LinkedList<String> getHouseImagePathList(int id) throws SQLException {
        LinkedList<String> imageList = new LinkedList<String>();
        String query = "select image_paths.image_path from house,image_paths,house_images where house.house_id =? and house.house_id = house_images.house_id and image_paths.image_id = house_images.image_id";
        PreparedStatement ps = connection.prepareStatement(query);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            imageList.add(rs.getString("IMAGE_PATH"));

        }



        return imageList;

    }

    public List<House> getHouseList() throws SQLException {
        List<House> houseList = new ArrayList<House>();
        String query = "select h.HOUSE_ID,r.ROOM_TYPE,h.TYPE,h.CUSTOMER_TC_ID,h.PRICE,h.DESCRIPTION,h.HASCOMBI,h.HASDOUBLEGLAZING,";
        query += "h.HASBALCONY,h.HASCABLETV,h.HASELEVATOR,h.HASFIREALARM,h.HASPARK,h.SOLD,h.AGENT_TC_ID,h.SELL_DATE,p.NAME as pname,d.NAME as dname from house h ";
        query += "left join room_type r on  h.room_Id=r.room_Id ";
        query += "inner join province p on h.p_Id=p.p_Id ";
        query += "inner join district d on h.d_Id=d.d_Id where h.SOLD = 'N' or h.SOLD = 'n'";
        Statement statement = connection.prepareStatement(query);
        ResultSet rs = statement.executeQuery(query);
        LinkedList<String> imageLinkedList = new LinkedList<String>();
        while (rs.next()) {

            java.util.Date sellDate = rs.getDate("SELL_DATE");
            boolean combi = ("y".equals(rs.getString("HASCOMBI")) || "Y".equals(rs.getString("HASCOMBI"))) ? true : false;
            boolean glazing = ("y".equals(rs.getString("HASDOUBLEGLAZING")) || "Y".equals(rs.getString("HASDOUBLEGLAZING"))) ? true : false;
            boolean balcony = ("y".equals(rs.getString("HASBALCONY")) || "Y".equals(rs.getString("HASBALCONY"))) ? true : false;
            boolean cabletv = ("y".equals(rs.getString("HASCABLETV")) || "Y".equals(rs.getString("HASCABLETV"))) ? true : false;
            boolean elevator = ("y".equals(rs.getString("HASELEVATOR")) || "Y".equals(rs.getString("HASELEVATOR"))) ? true : false;
            boolean firealarm = ("y".equals(rs.getString("HASFIREALARM")) || "Y".equals(rs.getString("HASFIREALARM"))) ? true : false;
            boolean park = ("y".equals(rs.getString("HASPARK")) || "Y".equals(rs.getString("HASPARK"))) ? true : false;
            boolean sold = ("y".equals(rs.getString("SOLD")) || "Y".equals(rs.getString("SOLD"))) ? true : false;
            String type = ("1".equals(rs.getString("TYPE")) ? "Sale" : "Rent");

            imageLinkedList = getHouseImagePathList(rs.getInt("HOUSE_ID"));
            houseList.add(new House(rs.getInt("HOUSE_ID"), rs.getString("ROOM_TYPE"), rs.getInt("PRICE"), type,
                    rs.getString("CUSTOMER_TC_ID"), combi,
                    glazing, balcony,
                    cabletv, elevator,
                    firealarm, park,
                    sold, rs.getString("AGENT_TC_ID"), sellDate, rs.getString("pname"), rs.getString("dname"), rs.getString("DESCRIPTION"), imageLinkedList));
        }
        return houseList;
    }

    public void deleteAgent(Agent agent) throws SQLException {
        String deleteAgentStoredProcedure = "{ call DELETE_AGENT(?) }";
        CallableStatement cs = connection.prepareCall(deleteAgentStoredProcedure);
        cs.setString(1, agent.getTC_ID());
        cs.execute();
    }
    //agent_id char,cust_id char,v_estate_id number

    public Agent agentLogin(String username, String password) throws SQLException {
        String query = "select a.USER_NAME,a.password,a.AGENT_TC_ID,p.name,p.surname,p.gsm_number,p.e_mail,p.birth_date,"
                + "p.address_resume,pro.NAME pname,d.name dname from agent a,person p,district d,province pro where "
                + "USER_NAME=? and PASSWORD=? and p.d_id = d.d_id and p.p_id = pro.p_id and a.AGENT_TC_ID=p.TC_ID";
        PreparedStatement statement = connection.prepareStatement(query);
        statement.setString(1, username);
        statement.setString(2, password);
        ResultSet rs = statement.executeQuery();
        Agent agent = null;
        if (rs != null) {
            while (rs.next()) {
                // String userName, String password, String TC_ID, String name, String surname, String gsmNumber, String eMail, Date birthDate, String addressDistrict, String addressResume)
                java.util.Date birthDate = rs.getDate("birth_date");

                agent = new Agent(rs.getString("USER_NAME"), rs.getString("password"), rs.getString("AGENT_TC_ID"), 
                        rs.getString("name"), rs.getString("surname"), rs.getString("gsm_number"), rs.getString("E_MAIL"), 
                        birthDate, rs.getString("dname"), rs.getString("address_resume"), rs.getString("pname"));
            }
        }

        return agent;
    }

    public void agentSellEstate(String agent_id, String customer_id, int estate_id) throws SQLException {
        String agentSellStoredProcedure = "{ call SELL_ESTATE(?,?,?) }";
        CallableStatement cs = connection.prepareCall(agentSellStoredProcedure);
        cs.setString(1, agent_id);
        cs.setString(2, customer_id);
        cs.setInt(3, estate_id);
        cs.execute();
    }

    public void agentSellHouse(String agent_id, String customer_id, int house_id) throws SQLException {
        String agentSellStoredProcedure = "{ call SELL_HOUSE(?,?,?) }";
        CallableStatement cs = connection.prepareCall(agentSellStoredProcedure);
        cs.setString(1, agent_id);
        cs.setString(2, customer_id);
        cs.setInt(3, house_id);
        cs.execute();
    }

    public void disconnect() throws SQLException {
        connection.close();
    }
}
